﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
//using System.Threading.Tasks;
using System.Net.Sockets;
using System.Net;
using System.Threading;
using System.Text.RegularExpressions;
using System.Globalization;
using System.Data.SQLite;
using MySql.Data.MySqlClient;
using Urd.Common;
using Urd.Packet;
namespace Urd.Common
{
    

    public class sync_cached_manager:IDisposable
    {
        //public delegate packet Process(cache c,packet p = null);

        #region fields
        System.Globalization.CultureInfo customCulture;

        protected string managername, tablename;
        public string Name { get { return managername; } }
        public string Table { get { return tablename; } }

        protected IPAddress address;
        public string Address { get { return address.ToString(); } }

        protected int port;
        public string Port { get { return port.ToString(); } }

        protected Opcode opcode_to_accept;
        public string Code { get { return opcode_to_accept.ToString(); } }

        int pps;
        public string PacketPerSecond { get { return pps.ToString(); ; } }

        string tableshema,shemasfolder,tablesfolder;
        List<string> columnnames;
        protected worker.Process process;
        TcpListener listener;        
        bool working, listen;
        public bool Working { get { return working; } }
        Thread /*worker,*/ netthread;
        System.Threading.Timer timer,timer2;
        
        cache sqlcache;
        public cache Cache { get { return sqlcache; } }

        #endregion
        public void Dispose() 
        {
            sqlcache.Dispose();
            
        }
        public void stop()
        {
            working = false;
            stop_cache();
            stop_network();

            
            columnnames = null;
            

        }
        public void start()
        {
            pps = 0;
            columnnames = new List<string>();

            ///
            start_network();

            ///
            start_cache(false);

            timer2 = new Timer((object o) => { pps = 0; }, null, 5000, 5000);
            working = true;
            //register in manager list here
        }

        public sync_cached_manager(managerinfo info, worker.Process work, string tablename = "", string tablesfolder=@"tables\",string shemasfolder=@"shemas\", string SQLshema = "")
        {
            customCulture = (System.Globalization.CultureInfo)System.Threading.Thread.CurrentThread.CurrentCulture.Clone();
            customCulture.NumberFormat.NumberDecimalSeparator = ".";
            opcode_to_accept = info.able_to_accept;
            managername = info.managername;
            address = info.address;
            port = info.port;
            process = work;
            this.tablename = tablename;
            this.tablesfolder = tablesfolder;
            this.shemasfolder = shemasfolder;
            
        }
       

        #region network funk

        private void start_network()
        {
            try
            {
                
                listener = new TcpListener(address, port);
                listener.Start();
                listen = true;
                netthread = new Thread(new ThreadStart(acceptClients));
                netthread.Name = managername + " network thread";
                netthread.Start();

            }
              catch (Exception e) { debug.Log("["+managername+"][network]:" + e.Source + " " + e.Message, debug.loglevel.Errors); }
        }

        private void stop_network() 
        {
            if (listener != null)
            {
                listen = false;
                netthread.Abort();
                listener.Stop();
                listener = null;
            }
        }

        void acceptClients()
        {

            if (!listen)
            {

                listener.Stop();
            }
            while (listen)
            {
                TcpClient client = null;
                NetworkStream stream = null;
                try
                {

                    if (listener.Pending())
                    {

                        client = listener.AcceptTcpClient();
                        EndPoint ipe = client.Client.RemoteEndPoint;

                        byte[] buffer = new byte[65];
                        stream = client.GetStream();

                        int i;
                        if ((i = stream.Read(buffer, 0, buffer.Length)) != 0)
                        {
                            packet p = new packet(buffer);
                            if (p.code != opcode_to_accept)
                            {

                                stream.WriteByte((byte)Opcode.Error);
                                stream.WriteByte((byte)Command.WrongManager);
                                return;
                            }
                            byte[] buf = { 0 };

                            System.Threading.Thread.CurrentThread.CurrentCulture = customCulture;
                            //pass packet to delegate who does alll work
                            buf = this.process(sqlcache,p).Bytes;

                            stream.Write(buf, 0, buf.Length);
                            pps++;
                        }
                        stream.Close();
                        client.Close();
                    }
                    else
                        Thread.Sleep(2);
                }
                catch (Exception e)
                {

                    debug.Log(e.Message, debug.loglevel.Errors);

                }
                finally
                {
                    if (stream != null) stream.Close();
                    if (client != null) client.Close();
                }
            }
        }
        
        #endregion

        #region cache funk
        public void dump()
        {
            if (!System.IO.Directory.Exists(tablesfolder)) System.IO.Directory.CreateDirectory(tablesfolder);
            sqlcache.DumpTable(tablesfolder + tablename + ".db");
        }
        private void start_cache(bool take_from_mysql)
        {
            if (tablename == "") tablename = managername;
            sqlcache = new cache();
            if (take_from_mysql)
            {
                prepare_shema();
                prepare_cache(true);
            }
            else 
            {
                string filename = tablesfolder + tablename + ".db";
                SQLiteConnection con = new SQLiteConnection("Data Source=" + filename + ";Version=3;New=True;");
                con.Open();
                con.BackupDatabase(sqlcache.Connection, "main", "main", -1, null, -1);
                con.Close();
            }
            timer = new System.Threading.Timer(new TimerCallback(sync), null, 4000, 4000);
        }
        private void stop_cache()
        {
            if (sqlcache != null)
            {
                timer.Dispose();
                sqlcache.close();
                sqlcache = null;
            }
        }
        private void prepare_shema()
        {

            string database_schema = "";

            object[] schema_unformatted
                = mysql.Sql("show create table " + tablename, true);

            database_schema = schema_unformatted[1] as string;

            string pattern = @"`\w+`";

            MatchCollection matches = Regex.Matches(database_schema, pattern);

            foreach (Match m in matches)
            {
                string val = m.Value.Replace("`", "");
                if (!columnnames.Contains(val)) columnnames.Add(val);
            }

            int end = database_schema.LastIndexOf(')') + 2;

            database_schema = database_schema.Remove(end);
            database_schema = database_schema.Replace("unsigned", " ");
            database_schema = database_schema.Replace("AUTO_INCREMENT", "");

            tableshema = database_schema;


        }

        private void prepare_cache(bool backup)
        {
            try
            {
                System.Threading.Thread.CurrentThread.CurrentCulture = customCulture;
                //here we can backup create table info
                if (backup)
                {
                    if (!System.IO.Directory.Exists(shemasfolder)) System.IO.Directory.CreateDirectory(shemasfolder);
                    System.IO.File.WriteAllText(shemasfolder + tablename + ".sql", tableshema);
                }
                sqlcache.Sql(tableshema, false);

                int max = 0;
                object ret = mysql.Sql("select max(id) from " + tablename, true)[0];
                if (ret.GetType() != typeof(System.DBNull))
                {
                    max = (int)ret;
                    for (int i = 0; i <= max; i++)
                    {
                        string values = "";
                        object[] val = mysql.Sql("select * from " + tablename + " where id=" + i.ToString(), true);
                        int l = val.Length;
                        for (int c = 0; c < l; c++)
                        {
                            if (val[c] != null)
                            {
                                string temp = val[c].ToString();
                                if (temp == "") temp = "null";
                                int ii;
                                float ff;
                                if (!Int32.TryParse(temp, out ii) && !Single.TryParse(temp, out ff) && temp != "null") temp = "'" + temp + "'";
                                values += temp;
                                if (c < l - 1) values += ",";
                            }
                        }
                        if (values != "")
                            sqlcache.Sql("insert into " + tablename + " values(" + values + ")", false);
                    }
                }
            }
            catch (MySqlException e) { Urd.Common.debug.Log("[prepare_cache][cache][mysql]:" + e.Source + " " + e.Message, Urd.Common.debug.loglevel.Errors); }
            catch (SQLiteException e) { Urd.Common.debug.Log("[prepare_cache][cache][sqlite]:" + e.Source + " " + e.Message, Urd.Common.debug.loglevel.Errors); }
            catch (Exception e) { Urd.Common.debug.Log("[prepare_cache][cache][system]:" + e.Source + " " + e.Message, Urd.Common.debug.loglevel.Errors); }


        }
        private void sync(object o)
        {
            try
            {
                System.Threading.Thread.CurrentThread.CurrentCulture = customCulture;

                string query = "";
                long max = 0;
                object[] temp = sqlcache.Sql("select max(id) from " + tablename, true);

                if (temp[0].GetType() != typeof(System.DBNull))
                {
                    max = (long)temp[0];
                    long min = (long)sqlcache.Sql("select max(id) from " + tablename, true)[0];
                    for (long i = min; i <= max; i++)
                    {
                        string askquery = "";

                        for (int cc = 1; cc < columnnames.Count; cc++)
                        {
                            askquery += "'" + columnnames[cc] + "'";

                            if (cc < columnnames.Count - 1) askquery += ",";
                        }
                        object[] val = sqlcache.Sql("select * from " + tablename + " where id=" + i.ToString(), true);
                        int l = val.Length;
                        if (l == columnnames.Count - 1)
                        {
                            foreach (object ob in val) { if (ob == null) return; }

                            query += "update ";
                            query += columnnames[0];
                            query += " set ";
                            for (int cc = 0; cc < val.Length; cc++)
                            {                                
                                if (!(val[cc].GetType() == typeof(DBNull)))
                                {
                                    query += columnnames[cc + 1] + " = '" + val[cc] + "'";
                                    if (cc < val.Length - 1) query += " , ";
                                }
                            }



                            query += " where id=" + val[0].ToString() + ";";
                        }
                        //cache.Sql("insert into pos values(" + values + ")", false);
                        if (query != "") mysql.Sql(query, false);
                    }
                    
                }

            }
            catch (MySqlException e) { Urd.Common.debug.Log("[cache][mysql]:" + e.Source + " " + e.Message, Urd.Common.debug.loglevel.Errors); }
            catch (SQLiteException e) { Urd.Common.debug.Log("[cache][sqlite]:" + e.Source + " " + e.Message, Urd.Common.debug.loglevel.Errors); }
            catch (Exception e) { Urd.Common.debug.Log("[cache][system]:" + e.Source + " " + e.Message, Urd.Common.debug.loglevel.Errors); }
        }

        #endregion
    }
}


